﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;


namespace Repository
{
    public class SupplierManagementRepository : ISupplierManagementRepository
    {
        private readonly IDbConnection _dbConnection; // 注入数据库连接

        /// <summary>
        /// 构造函数注入IDbConnection（在Program.cs中已注册）
        /// </summary>
        /// <param name="dbConnection"></param>
        public SupplierManagementRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        /// <summary>
        /// 实现新增供应商
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        public async Task AddAsync(WMSBasicSupplierManagement supplier)
        {
            supplier.create_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            // SQL插入语句（根据实际表结构调整）
            string sql = @"
                INSERT INTO WMS_basic_supplier_management (
                    supplier_code, supplier_name, indInd, supplier_classification,
                    contact_person, contact_phone_number, email_address, purchasing_department_id,
                    purchasing_manager_id, suppliers_address, account_name, taxid, bank_account,
                    opening_bank, bkaddr, status, note, create_by, create_time, annex_code
                )
                VALUES (
                    @supplier_code, @supplier_name, @indInd, @supplier_classification,
                    @contact_person, @contact_phone_number, @email_address, @purchasing_department_id,
                    @purchasing_manager_id, @suppliers_address, @account_name, @taxid, @bank_account,
                    @opening_bank, @bkaddr, @status, @note, @create_by, @create_time, @annex_code
                )";

            // 执行插入
            await _dbConnection.ExecuteAsync(sql, supplier);
        }

        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task Delete(string supplier_code)
        {
            string sql = @"UPDATE dbo.wms_basic_supplier_management 
                           SET 
                               is_delete = '1'
                           WHERE
                               supplier_code = @supplier_code";
            var param = new { supplier_code };

            await _dbConnection.ExecuteAsync(sql, param);
        }

        /// <summary>
        /// 供应商列表查询（SQL 优化：加排序、优化模糊查询、修正DTO映射）
        /// </summary>
        public async Task<List<SupplierListDTO>> GetAllSuppliersAsync(
            string supplierName = null,
            string status = null,
            int? industryId = null,
            string managerName = null)
        {

            string sql = @"
                SELECT 
                    -- 基础信息（映射SupplierListDTO字段）
                    s.supplier_code AS code,
                    s.supplier_name AS name,
                    ind.DictLabel AS industry,
                    sc.DictLabel AS category,
                    s.contact_person AS contact,
                    s.contact_phone_number AS phone,
                    s.email_address AS email,
                    dept.organization_name AS dept,
                    manager.user_name AS manager,
                    status_dict.DictLabel AS status,  -- 状态用字典翻译后的值
                    -- 地址与财务信息
                    s.suppliers_address AS address,
                    s.account_name AS accountName,
                    s.taxid AS taxid,
                    s.bank_account AS bankAccount,
                    s.opening_bank AS openingBank,
                    s.bkaddr AS bankAddress,
                    -- 操作信息
                    s.create_by AS createBy,
                    s.create_time AS createTime
                FROM 
                    dbo.wms_basic_supplier_management s
                -- 关联所属行业字典（Industry类型）
                LEFT JOIN 
                    dbo.wms_Dictionary_data ind 
                    ON s.indInd = ind.DictValue 
                    AND ind.DictTypeCode = 'Industry'
                    AND ind.Status = '0'  -- 字典数据正常状态
                -- 关联供应商分类字典（SupplierClassification类型）
                LEFT JOIN 
                    dbo.wms_Dictionary_data sc 
                    ON s.supplier_classification = sc.DictValue 
                    AND sc.DictTypeCode = 'SupplierClassification'
                    AND sc.Status = '0'  -- 字典数据正常状态
                -- 关联供应商状态字典（EnableDisable类型，翻译1=启用/0=禁用）
                LEFT JOIN 
                    dbo.wms_Dictionary_data status_dict 
                    ON s.status = status_dict.DictValue 
                    AND status_dict.DictTypeCode = 'EnableDisable'
                    AND status_dict.Status = '0'  -- 字典数据正常状态
                -- 关联进货部门（组织机构表）
                LEFT JOIN 
                    dbo.sys_org_organizations dept
                    ON s.purchasing_department_id = dept.organization_id 
                    AND dept.is_delete = '0'  -- 部门未删除
                    AND dept.status = '1'     -- 部门启用
                -- 关联进货负责人（用户表）
                LEFT JOIN 
                    dbo.sys_org_user_management manager 
                    ON s.purchasing_manager_id = manager.user_id 
                    AND manager.is_delete = '0'  -- 用户未删除
                    AND manager.status = '1'     -- 用户启用
                WHERE 
                    1=1
                    -- 新增：只查询未删除的供应商（is_delete=0）
                    AND s.is_delete = '0'
                    -- 供应商名称/编码模糊查询
                    AND (@supplierName IS NULL 
                         OR s.supplier_code LIKE CONCAT('%', @supplierName, '%') 
                         OR s.supplier_name LIKE CONCAT('%', @supplierName, '%'))
                    -- 供应商状态筛选（传参为状态编码，如1=启用/2=禁用）
                    AND (@status IS NULL OR s.status = @status)
                    -- 所属行业筛选（传参为行业ID）
                    AND (@industryId IS NULL OR s.indInd = @industryId)
                    -- 进货负责人姓名模糊查询
                    AND (@managerName IS NULL 
                         OR manager.user_name LIKE CONCAT('%', @managerName, '%'))
                -- 按创建时间倒序
                ORDER BY 
                    s.create_time DESC";

            var param = new
            {
                supplierName,
                status,
                industryId,
                managerName
            };

            return (await _dbConnection.QueryAsync<SupplierListDTO>(sql, param)).AsList();
        }
        public async Task<List<SupplierListDTO>> GetAllSuppliersAsync()
        {
            string sql = @"select supplier_code,supplier_name AS name,supplier_id AS id,
                           contact_person As contact,contact_phone_number As phone
                           from wms_basic_supplier_management
                           where is_delete = '0' and status = '1' ";

            return (await _dbConnection.QueryAsync<SupplierListDTO>(sql)).AsList();
        }

        public async Task<WMSBasicSupplierManagement> GetSupplierByCodeAsync(string supplierCode)
        {
            string sql = @"
                SELECT * FROM WMS_basic_supplier_management 
                WHERE supplier_code = @supplierCode";

            return await _dbConnection.QueryFirstOrDefaultAsync<WMSBasicSupplierManagement>(sql, new { supplierCode });
        }

        public async Task<WMSBasicSupplierManagement> GetSupplierByIdAsync(long supplierId)
        {
            string sql = @"select * from WMS_basic_supplier_management where
                           supplier_id = @supplierId";
            return await _dbConnection.QueryFirstOrDefaultAsync<WMSBasicSupplierManagement>(sql, new { supplierId });
        }

        /// <summary>
        /// 供应商编辑
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        public async Task UpdateAsync(WMSBasicSupplierManagement supplier)
        {
            // 设置修改时间（格式与创建时间一致）
            supplier.update_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            // 执行上述UPDATE语句
            string sql = @"
                UPDATE dbo.wms_basic_supplier_management
                SET 
                    supplier_name = @supplier_name,
                    indInd = @indInd,
                    supplier_classification = @supplier_classification,
                    contact_person = @contact_person,
                    contact_phone_number = @contact_phone_number,
                    email_address = @email_address,
                    purchasing_department_id = @purchasing_department_id,
                    purchasing_manager_id = @purchasing_manager_id,
                    suppliers_address = @suppliers_address,
                    account_name = @account_name,
                    taxid = @taxid,
                    bank_account = @bank_account,
                    opening_bank = @opening_bank,
                    bkaddr = @bkaddr,
                    status = @status,
                    note = @note,
                    update_by = @update_by,
                    update_time = @update_time,
                    annex_code = @annex_code
                WHERE 
                    supplier_code = @supplier_code";

            // 执行更新
            await _dbConnection.ExecuteAsync(sql, supplier);
        }
    }
}
